﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using JinianNet.JNCms.Data;

namespace JinianNet.JNCms.SQLiteDAL
{
    public class Article : BaseDAL, IArticle
    {
        public int Insert(Models.Article model)
        {
            if (model.ID <= 0)
                model.ID = Db.CreateKey("Article");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@Title", DbType.String,200),
					new SQLiteParameter("@KeyWords", DbType.String,300),
					new SQLiteParameter("@Description", DbType.String,600),
					new SQLiteParameter("@Tag", DbType.String,300),
					new SQLiteParameter("@Content", DbType.String),
					new SQLiteParameter("@ChannelID", DbType.Int32,4),
					new SQLiteParameter("@ChannelName", DbType.String,200),
					new SQLiteParameter("@KeyName", DbType.String,255),
					new SQLiteParameter("@TempLate", DbType.String,255),
					new SQLiteParameter("@CreateTime", DbType.DateTime),
					new SQLiteParameter("@EditTime", DbType.DateTime),
					new SQLiteParameter("@Source", DbType.String,50),
					new SQLiteParameter("@Author", DbType.String,20),
					new SQLiteParameter("@Editor", DbType.String,20),
					new SQLiteParameter("@Url", DbType.String,50),
					new SQLiteParameter("@Click", DbType.Int32,4),
					new SQLiteParameter("@IsNeedPassword", DbType.Boolean,1),
					new SQLiteParameter("@IsLock", DbType.Boolean,1),
					new SQLiteParameter("@IsVerify", DbType.Boolean,1),
					new SQLiteParameter("@UserID", DbType.Int32,4),
					new SQLiteParameter("@IsUrl", DbType.Boolean,1),
					new SQLiteParameter("@IsTop", DbType.Boolean,1),
					new SQLiteParameter("@Password", DbType.String,255),
                    new SQLiteParameter("@IsImg", DbType.Boolean,1),
					new SQLiteParameter("@Src", DbType.String,255),
					new SQLiteParameter("@ID", DbType.Int32,4)};
            parameters[0].Value = model.Title;
            parameters[1].Value = model.KeyWords;
            parameters[2].Value = model.Description;
            parameters[3].Value = model.Tag;
            parameters[4].Value = model.Content;
            parameters[5].Value = model.ChannelID;
            parameters[6].Value = model.ChannelName;
            parameters[7].Value = model.KeyName;
            parameters[8].Value = model.TempLate;
            parameters[9].Value = model.CreateTime;
            parameters[10].Value = model.EditTime;
            parameters[11].Value = model.Source;
            parameters[12].Value = model.Author;
            parameters[13].Value = model.Editor;
            parameters[14].Value = model.Url;
            parameters[15].Value = model.Click;
            parameters[16].Value = model.IsNeedPassword;
            parameters[17].Value = model.IsLock;
            parameters[18].Value = model.IsVerify;
            parameters[19].Value = model.UserID;
            parameters[20].Value = model.IsUrl;
            parameters[21].Value = model.IsTop;
            parameters[22].Value = model.Password;
            parameters[23].Value = model.IsImg;
            parameters[24].Value = model.Src;
            parameters[25].Value = model.ID;
            if (Db.Helper.ExecuteNonQuery(CommandType.Text, "insert into JNCms_Article([Title],[KeyWords],[Description],[Tag],[Content],[ChannelID],[ChannelName],[KeyName],[TempLate],[CreateTime],[EditTime],[Source],[Author],[Editor],[Url],[Click],[IsNeedPassword],[IsLock],[IsVerify],[UserID],[IsUrl],[IsTop],[Password],[IsImg],[Src]) values (@Title,@KeyWords,@Description,@Tag,@Content,@ChannelID,@ChannelName,@KeyName,@TempLate,@CreateTime,@EditTime,@Source,@Author,@Editor,@Url,@Click,@IsNeedPassword,@IsLock,@IsVerify,@UserID,@IsUrl,@IsTop,@Password,@IsImg,@Src)", parameters) > 0)
            {
                Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Category set [Count]=[Count]+1 where [ID]=@ID", new SQLiteParameter("@ID", model.ID));
                return 1;
            }
            return 0;
            //int result = 
            //if (result > 0) {
            //    DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("update [JNCms_Category] set [Count]=[Count]+{0} where [ID]={1}", result, model.ChannelID), null);
            //}
            //return result;
        }

        public int Update(Models.Article model)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@Title", DbType.String,200),
					new SQLiteParameter("@KeyWords", DbType.String,300),
					new SQLiteParameter("@Description", DbType.String,600),
					new SQLiteParameter("@Tag", DbType.String,300),
					new SQLiteParameter("@Content", DbType.String),
					new SQLiteParameter("@ChannelID", DbType.Int32,4),
					new SQLiteParameter("@ChannelName", DbType.String,200),
					new SQLiteParameter("@KeyName", DbType.String,255),
					new SQLiteParameter("@TempLate", DbType.String,255),
					//new SQLiteParameter("@CreateTime", DbType.DateTime),
					new SQLiteParameter("@EditTime", DbType.DateTime),
					new SQLiteParameter("@Source", DbType.String,50),
					new SQLiteParameter("@Author", DbType.String,20),
					new SQLiteParameter("@Editor", DbType.String,20),
					new SQLiteParameter("@Url", DbType.String,50),
					//new SQLiteParameter("@Click", DbType.Int32,4),
					new SQLiteParameter("@IsNeedPassword", DbType.Boolean,1),
					new SQLiteParameter("@IsLock", DbType.Boolean,1),
					new SQLiteParameter("@IsVerify", DbType.Boolean,1),
					new SQLiteParameter("@UserID", DbType.Int32,4),
					new SQLiteParameter("@IsUrl", DbType.Boolean,1),
					new SQLiteParameter("@IsTop", DbType.Boolean,1),
					new SQLiteParameter("@Password", DbType.String,255),
                    new SQLiteParameter("@IsImg", DbType.Boolean,1),
					new SQLiteParameter("@Src", DbType.String,255),
                    new SQLiteParameter("@ID", DbType.Int32,4)};

            parameters[0].Value = model.Title;
            parameters[1].Value = model.KeyWords;
            parameters[2].Value = model.Description;
            parameters[3].Value = model.Tag;
            parameters[4].Value = model.Content;
            parameters[5].Value = model.ChannelID;
            parameters[6].Value = model.ChannelName;
            parameters[7].Value = model.KeyName;
            parameters[8].Value = model.TempLate;
            //parameters[10].Value = model.CreateTime;
            parameters[9].Value = model.EditTime;
            parameters[10].Value = model.Source;
            parameters[11].Value = model.Author;
            parameters[12].Value = model.Editor;
            parameters[13].Value = model.Url;
            //parameters[14].Value = model.Click;
            parameters[14].Value = model.IsNeedPassword;
            parameters[15].Value = model.IsLock;
            parameters[16].Value = model.IsVerify;
            parameters[17].Value = model.UserID;
            parameters[18].Value = model.IsUrl;
            parameters[19].Value = model.IsTop;
            parameters[20].Value = model.Password;
            parameters[21].Value = model.IsImg;
            parameters[22].Value = model.Src;
            parameters[23].Value = model.ID;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Article set [Title]=@Title,[KeyWords]=@KeyWords,[Description]=@Description,[Tag]=@Tag,[Content]=@Content,[ChannelID]=@ChannelID,[ChannelName]=@ChannelName,[KeyName]=@KeyName,[TempLate]=@TempLate,[EditTime]=@EditTime,[Source]=@Source,[Author]=@Author,[Editor]=@Editor,[Url]=@Url,[IsNeedPassword]=@IsNeedPassword,[IsLock]=@IsLock,[IsVerify]=@IsVerify,[UserID]=@UserID,[IsUrl]=@IsUrl,[IsTop]=@IsTop,[Password]=@Password,[IsImg]=@IsImg,[Src]=@Src where [ID]=@ID", parameters);
        }

        public int Delete(int id)
        {
            SQLiteParameter parameters = new SQLiteParameter("@ID", DbType.Int32, 4);
            parameters.Value = id;
            //object value = 
            int value = Db.Helper.ExecuteNonQuery(CommandType.Text, "delete from JNCms_Article where [ID]=@ID", new SQLiteParameter("@ID", id));
            if (value > 0)
                Db.Helper.ExecuteScalar(CommandType.Text, string.Concat("update JNCms_Category set [Count]=[Count]-", value.ToString(), " where [ID]=(select ChannelID from JNCms_Article where [ID]=@ID limit 1) and [Count]>0"), parameters);
            return value;
        }

        public List<Models.Article> GetList(int id, int pageIndex, int pageSize)
        {
            List<Models.Category> array = new Category().GetList(id);
            string[] channel;
            if (array.Count > 0)
            {
                channel = new string[array.Count + 1];
                channel[0] = id.ToString();
                for (int i = 0; i < array.Count; i++)
                {
                    channel[i + 1] = array[i].ID.ToString();
                }
            }
            else
            {
                channel = new string[] { id.ToString() };
            }


            List<Models.Article> list = new List<Models.Article>();
            if (pageSize > 0)
            {
                string sql;
                if (pageIndex == 1)
                    sql = pageSize.ToString();
                else
                    sql = string.Concat(Convert.ToString(pageSize * (pageIndex - 1)), ",", Convert.ToString(pageSize * pageIndex));

                using (IDataReader rd = Db.Helper.ExecuteReader(string.Concat("select * from [JNCms_Article] where [ChannelID]", channel.Length == 1 ? string.Concat("=", id.ToString()) : string.Concat(" in (", string.Join(",", channel), ")"), " order by [CreateTime] desc limit ", sql)))
                {

                    while (rd.Read())
                    {
                        list.Add(Db.Entity.PopulateEntity<Models.Article>(rd));
                    }
                    //list = Db.Entity.PopulateEntityCollection <Models.Article>(rd);
                }

            }
            return list;
        }

        public Models.Article GetItem(string item)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from [JNCms_Article] where [KeyName]=@ID limit 1", new SQLiteParameter("@ID", item)))
            {
                if (rd.Read())
                {
                    return Db.Entity.PopulateEntity<Models.Article>(rd);
                }
            }
            return null;
        }



        public void Click(int id)
        {
            Db.Helper.ExecuteNonQuery(CommandType.Text, "update [JNCms_Article] set [Click]=[Click]+1 where [ID]=@ID", new SQLiteParameter("@ID", id));
        }
    }
}
